﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using RPAAction.Access_CSO;
using RPAAction.Data_CSO;

namespace RPAAction
{
    /// <summary>
    /// 数据传输合集
    /// </summary>
    public static class DataCSO
    {
        private static DbDataReader reader = null;
        private static event Action OnClearReader;

        /// <summary>
        /// 检查数据读取的缓存
        /// </summary>
        private static void CheckReader()
        {
            if (reader == null)
            {
                throw new Exception("数据读取的缓存为空请先执行 Read_ 系列的函数");
            }
        }

        /// <summary>
        /// 清除数据读取的缓存
        /// </summary>
        /// <param name="isOnClearReader"></param>
        private static void ClearReader(bool isOnClearReader = true)
        {
            if (isOnClearReader && OnClearReader != null)
            {
                OnClearReader();
            }
            reader = null;
            OnClearReader = null;
        }

        /// <summary>
        /// 读取 sqlserver
        /// </summary>
        /// <param name="DataSource">ip,port</param>
        /// <param name="DataBase">数据库名称</param>
        /// <param name="user"></param>
        /// <param name="pwd"></param>
        /// <param name="sql">读取数据的sql</param>
        public static void Read_SqlServer(string DataSource, string DataBase, string user, string pwd, string sql)
        {
            ClearReader(false);

            string connStr = $@"Data Source={DataSource};Initial Catalog={DataBase};User ID={user};Pwd={pwd};";
            SqlConnection connection = new SqlConnection(connStr);
            SqlCommand command = new SqlCommand(sql, connection);
            connection.Open();
            reader = command.ExecuteReader();
            OnClearReader += () => { using (connection) { } };


        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ExcelPath">工作簿路径,如果忽略则视为活动工作簿</param>
        /// <param name="Sheet">工作表,如果忽略则视为活动工作表</param>
        /// <param name="range">单元格,如果忽略视为已使用的单元格</param>
        /// <param name="MaxCashCount">最大缓存行数,每次读取Excel中的数据量</param>
        public static void Read_Excel(string ExcelPath = null, string Sheet = null, string range = "used", int MaxCashCount = 10000)
        {
            ClearReader(false);

            reader = new ExcelDataReader(ExcelPath, Sheet, range, MaxCashCount);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheet">工作表名称,如果为空则取第一个</param>
        public static void Read_NPOIExcel(string filePath, string sheet = "")
        {
            ClearReader(false);

            reader = new NPOIExcelDataReader(filePath, sheet);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="accessPath">Access文件路径</param>
        /// <param name="SQL">sql语句</param>
        public static void Read_Access(string accessPath, string SQL)
        {
            ClearReader(false);

            reader = new AccessDataReader(accessPath, SQL);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        public static void Read_DataTable(DataTable table)
        {
            ClearReader(false);

            reader = new DataTableDataReader(table);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="json"></param>
        public static void Read_Json(string json)
        {
            DataTable t = JsonConvert.DeserializeObject<DataTable>(json);
            Read_DataTable(t);
        }

        public static void Read_DuckDB(string dbPath, string SQL)
        {
            ClearReader(false);

            reader = new DuckDbDataReader(dbPath, SQL);
        }

        /// <summary>
        /// 导入 sqlserver
        /// </summary>
        /// <param name="DataSource">ip,port</param>
        /// <param name="DataBase">数据库名称</param>
        /// <param name="user"></param>
        /// <param name="pwd"></param>
        /// <param name="table"></param>
        /// <param name="appand">是否附加数据默认true,否则清空表</param>
        /// <param name="bulkCopyTimeout"></param>
        public static void Export_SqlServrt(string DataSource, string DataBase, string user, string pwd, string table, bool appand = true, int bulkCopyTimeout = 600)
        {
            CheckReader();

            RPADataExport.ImportDispose(
                reader,
                new SQLServerDataExport(DataSource, DataBase, user, pwd, table, appand, bulkCopyTimeout)
            );

            ClearReader();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="delimiter">分隔符,如果为空则判断文件后缀,csv为",",其余默认"\t"</param>
        /// <param name="withTitle">是否写入标题,默認True</param>
        public static void Export_TXT(string path, string delimiter = "", bool withTitle = true)
        {
            CheckReader();

            RPADataExport.ImportDispose(
                reader,
                new TXTDataExport(path, delimiter, withTitle)
            );

            ClearReader();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ExcelPath">工作簿路径,如果忽略则视为活动工作簿</param>
        /// <param name="Sheet">工作表,如果忽略则视为活动工作表</param>
        /// <param name="range">单元格,如果忽略视"A1"</param>
        /// <param name="withTitle">是否写入标题,默認True</param>
        /// <param name="MaxCashCount">最大缓存行数,每次写入Excel中的数据量</param>
        public static void Export_Excel(string ExcelPath = null, string Sheet = null, string range = "A1", bool withTitle = true, int MaxCashCount = 10000)
        {
            CheckReader();

            RPADataExport.ImportDispose(
                reader,
                new ExcelDataExport(ExcelPath, Sheet, range, withTitle, MaxCashCount)
            );

            ClearReader();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="accessPath">Access文件路径</param>
        /// <param name="table">Accesssu数据表</param>
        public static void Export_Access(string accessPath, string table)
        {
            CheckReader();

            RPADataExport.ImportDispose(
                reader,
                new AccessDataExport(accessPath, table)
            );

            ClearReader();
        }

        public static void Export_DuckDB(string dbPath, string table)
        {
            CheckReader();

            RPADataExport.ImportDispose(
                reader,
                new DuckDbDataExport(dbPath, table)
            );

            ClearReader();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static DataTable Export_DataTable()
        {
            CheckReader();

            DataTable dt = new DataTable();
            RPADataExport.ImportDispose(
                reader,
                new DataTableDataExport(dt)
            );

            ClearReader();

            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static string Export_Json()
        {
            DataTable t = Export_DataTable();
            return JsonConvert.SerializeObject(t);
        }

        /// <summary>
        /// Access 执行SQL并返回执行行数
        /// </summary>
        /// <param name="accessPath"></param>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public static decimal Access_GetAC(string accessPath, string SQL)
        {
            var a = new Sql_GetAc(accessPath, SQL);
            a.Run();
            return a.Count;
        }

        /// <summary>
        /// 执行Access函数
        /// </summary>
        /// <param name="accessPath"></param>
        /// <param name="foucName"></param>
        /// <param name="param1"></param>
        /// <param name="param2"></param>
        /// <param name="param3"></param>
        /// <param name="param4"></param>
        /// <param name="param5"></param>
        /// <param name="param6"></param>
        /// <param name="param7"></param>
        /// <param name="param8"></param>
        /// <param name="param9"></param>
        /// <param name="param10"></param>
        /// <returns></returns>
        public static string Access_RunVBAFunction(
            string accessPath,
            string foucName,
            string param1 = "",
            string param2 = "",
            string param3 = "",
            string param4 = "",
            string param5 = "",
            string param6 = "",
            string param7 = "",
            string param8 = "",
            string param9 = "",
            string param10 = ""
        )
        {
            var a = new Access_RunVBAFunction(accessPath, foucName, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10);
            a.Run();
            return a.Ret;
        }

        public static void Access_Close(string accessPath = null)
        {
            AccessAction.ClearUp(accessPath);
        }
    }
}
